/*
 * Class: DBMAgenda
 * 
 * Description: Implements all the statements to interact with the agendas
 * 				stored in the database
 * 
 * Version 1.0 M-A Lavoie - 2011-11-11
 * 		Initial implementation
 * 
 * Version 1.1 Mohamed Ali Ayed - 2011-11-16
 * 
 * Version 1.2 M-A Lavoie - 2011-12-04
 * 		Changed agendas list from HashSet to ArrayList
 * 
 * Version 1.3 Sonny Guenette - 2011-12-08
 * 		Changed variables and parameters names to respect the team naming convention
 * 
 * Version 1.3 Sonny Guenette - 2011-12-09
 * 		Added  new method "checkDuplicateName"
 * 
 * Copyright GPP - Genuine People Personality inc.
 * 
 */

package dbManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import calendarEventManager.CEMAgenda;

public class DBMAgenda{
	private PreparedStatement m_oPst;
	private ResultSet m_oRset;
	
	//**************************************************************
	// Method: createAgenda
	// Description: Add a new agenda in the database
	//**************************************************************
	/**
	 * @param p_oAgenda The agenda object that contain all the informations needed
	 * @param p_oCon The connection object
	 * 
	 * @return true if the agenda is successfully added
	 */
	public boolean createAgenda(CEMAgenda p_oAgenda, Connection p_oCon) {
		try {
			m_oPst = p_oCon.prepareStatement("INSERT INTO agendas (name,color) VALUES (?,?)");
			m_oPst.setString(1, p_oAgenda.getAgendaName());
			m_oPst.setInt(2, p_oAgenda.getAgendaColour());
			m_oPst.executeUpdate();

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMAgenda.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 

		return true;
	} // end createAgenda

	
	//**************************************************************
	// Method: nameAlreadyExist
	// Description: Check if there is already an agenda with this name in the database
	//**************************************************************
	/**
	 * @param p_sAgendaName The agenda name
	 * @param p_oCon The connection object
	 * 
	 * @return true if there is already an agenda with this name
	 */
	public boolean nameAlreadyExist(String p_sAgendaName, Connection p_oCon){
		try {
			m_oPst = p_oCon.prepareStatement("SELECT * FROM agendas WHERE name = ?");
			m_oPst.setString(1, p_sAgendaName);
			m_oRset = m_oPst.executeQuery();

			if(m_oRset.next())
				throw new SQLException();

		} catch (SQLException e) {	
			return true;
		}

		return false;
	}// end nameAlreadyExist()

	 //******************************************************
	 // Method: updateAgenda
	 // Description: Change the informations for an agenda 
	 //***************************************************** 
	 /**
	 * @param p_oAgenda The agenda object that contain all the informations needed
	 * @param p_oCon The connection object
	 * 
	 * @return true if the agenda is successfully modified
	 */
	public boolean updateAgenda(CEMAgenda p_oAgenda, Connection p_oCon){
		try {       
			m_oPst = p_oCon.prepareStatement("UPDATE agendas SET name= ?,color = ? WHERE agenda_id= ?");
			m_oPst.setString(1, p_oAgenda.getAgendaName());
			m_oPst.setInt(2, p_oAgenda.getAgendaColour());
			m_oPst.setInt(3, p_oAgenda.getAgendaId());
			m_oPst.executeUpdate();
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMAgenda.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	
	}//end updateAgenda

	 //******************************************************
	 // Method: deleteAgenda
	 // Description: Delete an agenda from the database 
	 //***************************************************** 
	 /**
	 * @param p_nAgendaId The agenda Id
	 * @param p_oCon The connection object
	 * 
	 * @return true if the agenda is successfully deleted
	 */
	public boolean deleteAgenda(int p_nAgendaId,  Connection p_oCon){
		try {       
			m_oPst = p_oCon.prepareStatement("DELETE FROM agendas WHERE agenda_id = ?");
			m_oPst.setInt(1, p_nAgendaId);
			m_oPst.executeUpdate();
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMAgenda.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	    	
	}//end deleteAgenda

	 //******************************************************
	 // Method: selectAllTupleAgenda
	 // Description: Select all agendas in the database
	 //***************************************************** 
	 /**
	 * @param p_oCon The connection object
	 * @param p_oAgendaList The list that will contain all the agendas
	 * 
	 * @return true if the agendas are successfully retrieved
	 */
	public boolean selectAllTupleAgenda(Connection p_oCon,
			List<CEMAgenda> p_oAgendaList ){
		try {       
			m_oPst = p_oCon.prepareStatement("SELECT * FROM agendas");
			m_oRset = m_oPst.executeQuery();
			while (m_oRset.next()) {
				CEMAgenda oNewAgenda = new CEMAgenda(m_oRset.getInt(1), m_oRset.getString(2), m_oRset.getInt(3));
				p_oAgendaList.add(oNewAgenda);
			}
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMAgenda.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	
	} // end selectAllTupleAgenda

	 //******************************************************
	 // Method: getAgendaId
	 // Description: Retrieve the Id of an agenda
	 //***************************************************** 
	 /**
	 * @param p_oAgenda The agenda object that contain all the informations needed
	 * @param p_oCon The connection object
	 * 
	 * @return the agenda id or -1 if there's an error
	 */
	public int getAgendaId(CEMAgenda p_oAgenda, Connection p_oCon){
		try{
			m_oPst = p_oCon.prepareStatement("SELECT agenda_id FROM agendas WHERE name=?");
			m_oPst.setString(1, p_oAgenda.getAgendaName());
			m_oRset = m_oPst.executeQuery();
			m_oRset.next();
			return m_oRset.getInt(1);
		} catch (SQLException ex) {
			return -1;
		}// end catch
	}// end getAgendaId

} //end class DBMAgenda